package doInterce.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import doInterce.DoInterface;
import org.w3c.dom.*;
import utils.DBUtil;
import utils.PathUtil;
import utils.YmlUtil;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * @Description:
 * @Author: syh
 * @Date: 2024/01/22/18:59
 */
public class DoParsingExcel implements DoInterface {

    @Override
    public void doExecute() {

        String dirPth=null;
        if(PathUtil.getJarPath().contains("jar")) {
            dirPth = PathUtil.getJarPath().replaceAll("[a-z_]+.jar", "");
        }else{
            dirPth = PathUtil.getJarPath();
        }
        if(dirPth.startsWith("/")){
            dirPth = dirPth.substring(1);
        }

        Map<String, Object> ymlMap = YmlUtil.getYmlMap();
        String url = Convert.toStr(ymlMap.get("url"));
        String userName = Convert.toStr(ymlMap.get("userName"));
        String password = Convert.toStr(ymlMap.get("password"));
        try(Connection connection = DBUtil.getConnection(url, userName, password)) {
            File dirFile = new File(dirPth);
            File[] files = dirFile.listFiles();
            for(File file:files){
                if(file.getName().contains("xml")){
                    List<String> xmlContent = getXmlContent(file, connection);
//                    String outPath = dirPth+file.getName().replace(".xml", ".sql");
                    int pageSize = 30000;
                    int fileCount = xmlContent.size() % pageSize == 0 ? xmlContent.size() / pageSize : (xmlContent.size() / pageSize)+1;
                    for(int i = 0; i < fileCount ;  i++){
                        String outPath = dirPth+file.getName().replace(".xml", i+".sql");
                        List<String> strList = new ArrayList<>();
                        for(int j = i*pageSize ; j < (i+1) * pageSize && j < xmlContent.size() ; j++){
                            strList.add(xmlContent.get(j));
                        }
                        FileUtil.writeLines(strList,outPath,"utf-8");
                    }
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }



    }

    public static  List<String>  getXmlContent(File file, Connection connection) throws Exception{
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        DocumentBuilder db = dbf.newDocumentBuilder();
        Document doc = db.parse(file);
        NodeList rows = doc.getElementsByTagName("row");

        List<String> sqlList = new ArrayList<>();
        String tableName = file.getName().substring(0, file.getName().length() - 4);
        List<String> numFields = getNumFields(tableName, connection);

        for (int j = 0; j < rows.getLength(); j++) {
            Element row = (Element) rows.item(j);

            NamedNodeMap attributes = row.getAttributes();
            List<String> nameList = new ArrayList<>();
            List<String> valueList = new ArrayList<>();
            for (int k = 0; k < attributes.getLength(); k++) {
                Node attribute = attributes.item(k);
                String nodeValue = attribute.getNodeValue();
                String nodeName = attribute.getNodeName();

                if ("".equals(nodeValue) && (numFields.contains(nodeName.toUpperCase()) || "A99Z1".equalsIgnoreCase(tableName))) {
                    nodeValue = "null";
                } else {
                    nodeValue = "'" + nodeValue + "'";
                }

                nameList.add(nodeName);
                valueList.add(nodeValue);
            }

            String nameStr = CollectionUtil.join(nameList, ",");
            String valueStr = CollectionUtil.join(valueList, ",");
            String sqlTemp = "insert into %s (%s) values(%s);";
            String sql = String.format(sqlTemp, tableName, nameStr, valueStr);

            sqlList.add(sql);
        }

        return sqlList;

    }


    public static List<String> getNumFields(String tableName, Connection conn){
        List<String> columns = new ArrayList<>();;
        try(PreparedStatement statement = conn.prepareStatement("DESCRIBE "+tableName)){
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                String colName = resultSet.getString(1);
                String colType = resultSet.getString(2);

                if(colType.contains("timestamp") || colType.contains("decimal") || colType.contains("int")){
                    columns.add(colName.toUpperCase());
                }
            }

            resultSet.close();
        }catch (Exception e){
            e.printStackTrace();
        }

        return columns;
    }
}
